﻿using System;
using System.Collections.Generic;
using System.Data.Entity.Core.Objects;
using System.Dynamic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
using TS2015.Models;

namespace TS2015.Areas.Admin.Controllers
{
    public class ThongKeController : Controller
    {
        TS2015Context db = new TS2015Context();

        // GET: Admin/ThongKe
        public ActionResult Index()
        {
            // Neu khong co quyen thi chuyen huong den trang tu choi truy cap
            //if (!CheckRole.IsRole("tk_index", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            return View();
        }

        public ActionResult HoSoDangKy()
        {
        //    if (!CheckRole.IsRole("tk_hsdk", Session["Function"] as string[]))
        //        return Redirect("~/accessdenied.html");

            if (Request.IsAjaxRequest())
                return PartialView();
            else
                return View();
        }

        public ActionResult DangKyTheoNganh()
        {
            //if (!CheckRole.IsRole("tk_dktn", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");
 
            ViewBag.KhoiThi = new SelectList(db.TbKhoiThis.ToList(), "MaKhoiThi", "TenKhoiThi");
            ViewBag.HeDaoTao = new SelectList(db.TbHeDaoTaos.ToList(),"MaHeDaoTao", "TenHeDaoTao");
            if (Request.IsAjaxRequest())
                return PartialView();
            else
                return View();
        }
        public ActionResult DangKyTheoChuyenNganh()
        {
            //if (!CheckRole.IsRole("tk_dktcn", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            ViewBag.HeDaoTao = new SelectList(db.TbHeDaoTaos.ToList(), "MaHeDaoTao", "TenHeDaoTao");
            ViewBag.KhoiThi = new SelectList(db.TbKhoiThis.ToList(), "MaKhoiThi", "TenKhoiThi");
            ViewBag.Nganh = new SelectList(db.TbNganhs.ToList(),"IDNganh", "TenNganh");
            if (Request.IsAjaxRequest())
                return PartialView();
            else
                return View();
        }
        public ActionResult DangKyTheoHDT()
        {
            //if (!CheckRole.IsRole("tk_dkthdt", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            if (Request.IsAjaxRequest())
                return PartialView();
            else
                return View();
        }
        public ActionResult DangKyTheoKhoiThi()
        {
            //if (!CheckRole.IsRole("tk_dktkt", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            if (Request.IsAjaxRequest())
                return PartialView();
            else
                return View();
        }

        [HttpPost]
        [ActionName("HoSoDangKy")]
        public ActionResult POST_HoSoDangKy(string TuNgay, string DenNgay, string NhomTheo)
        {
            //if (!CheckRole.IsRole("tk_hsdk", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");
            
            if (ModelState.IsValid)
            {
                ViewBag.TuNgay = TuNgay;
                ViewBag.DenNgay = DenNgay;
                DateTime _TuNgay = DateTime.ParseExact(TuNgay,"dd/MM/yyyy",null);
                DateTime _DenNgay = DateTime.ParseExact(DenNgay, "dd/MM/yyyy", null);
                ViewBag.NhomTheo = NhomTheo;
                var result = (from p in db.TbThiSinhs
                              join q in db.TbChuyenNganhs on p.MaChuyenNganh equals q.MaChuyenNganh
                              join h in db.TbHeDaoTaos on p.MaHeDaoTao equals h.MaHeDaoTao
                              join k in db.TbNganhs on q.IDNganh equals k.IDNganh
                              join l in db.TbKhoiThis on k.MaKhoiThi equals l.MaKhoiThi
                              //where 
                              orderby p.NgayDangKi
                              select new ResultTK
                              {
                                  MaTS = p.MaTS,
                                  FullName = p.HoDem + p.Ten,
                                  SBD = p.SBD,
                                  NgaySinh = p.NgaySinh,
                                  MaChuyenNganh = p.MaChuyenNganh,
                                  MaHeDaoTao = p.MaHeDaoTao,
                                  MaKhoiThi = l.MaKhoiThi,
                                  TenKhoiThi = l.TenKhoiThi,
                                  MaNganh = k.MaNganh,
                                  TenChuyenNganh = q.TenChuyenNganh,
                                  TenNganh = k.TenNganh,
                                  TenHeDaoTao = h.TenHeDaoTao,
                                  NgayDangKy = p.NgayDangKi
                              }
                              ).AsEnumerable().Where(p => p.NgayDangKy.Date >= _TuNgay.Date && p.NgayDangKy.Date <= _DenNgay.Date);
                return PartialView("_ThongKeHoSo", result.ToList());
            }
            else
                ModelState.AddModelError("Error", "Có lỗi.");

            return PartialView();
        }

        [HttpPost]
        [ActionName("DangKyTheoNganh")]
        public ActionResult POST_DangKyTheoNganh(string TuNgay, string DenNgay, string KhoiThi,string HeDaoTao)
        {
            //if (!CheckRole.IsRole("tk_dktn", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            if (ModelState.IsValid)
            {
                ViewBag.TuNgay = TuNgay;
                ViewBag.DenNgay = DenNgay;
                DateTime _TuNgay = DateTime.ParseExact(TuNgay, "dd/MM/yyyy", null);
                DateTime _DenNgay = DateTime.ParseExact(DenNgay, "dd/MM/yyyy", null);
                ViewBag.Khoi = KhoiThi;
                ViewBag.HeDaoTao = db.TbHeDaoTaos.Where(e => e.MaHeDaoTao.ToString() == HeDaoTao).First().TenHeDaoTao;

                var result = (from p in db.TbThiSinhs
                              join q in db.TbChuyenNganhs on p.MaChuyenNganh equals q.MaChuyenNganh
                              join h in db.TbHeDaoTaos on p.MaHeDaoTao equals h.MaHeDaoTao
                              join k in db.TbNganhs on q.IDNganh equals k.IDNganh
                              join l in db.TbKhoiThis on k.MaKhoiThi equals l.MaKhoiThi
                              where   k.MaKhoiThi == KhoiThi && p.MaHeDaoTao.ToString() == HeDaoTao 
                              select new ResultTK
                              {
                                  MaTS = p.MaTS,
                                  FullName = p.HoDem + p.Ten,
                                  SBD = p.SBD,
                                  NgaySinh = p.NgaySinh,
                                  MaChuyenNganh = p.MaChuyenNganh,
                                  MaHeDaoTao = p.MaHeDaoTao,
                                  MaKhoiThi = l.MaKhoiThi,
                                  TenKhoiThi = l.TenKhoiThi,
                                  MaNganh = k.MaNganh,
                                  TenChuyenNganh = q.TenChuyenNganh,
                                  TenNganh = k.TenNganh,
                                  TenHeDaoTao = h.TenHeDaoTao,
                                  NgayDangKy = p.NgayDangKi
                              }
                              ).AsEnumerable().Where(p => p.NgayDangKy.Date >= _TuNgay.Date && p.NgayDangKy.Date <= _DenNgay.Date); ;

                return PartialView("_DangKyTheoNganh", result.ToList());
            }
            else
                ModelState.AddModelError("Error", "Có lỗi.");

            return PartialView();
        }

        [HttpPost]
        [ActionName("DangKyTheoChuyenNganh")]
        public ActionResult POST_DangKyTheoChuyenNganh(string TuNgay, string DenNgay,string HeDaoTao, string KhoiThi,string Nganh)
        {
            //if (!CheckRole.IsRole("tk_dktcn", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            if (ModelState.IsValid)
            {
                ViewBag.TuNgay = TuNgay;
                ViewBag.DenNgay = DenNgay;
                DateTime _TuNgay = DateTime.ParseExact(TuNgay, "dd/MM/yyyy", null);
                DateTime _DenNgay = DateTime.ParseExact(DenNgay, "dd/MM/yyyy", null);
                ViewBag.Khoi = KhoiThi;
                ViewBag.TenNganh = db.TbNganhs.Where(e => e.IDNganh.ToString() == Nganh).First().TenNganh;
                ViewBag.HeDaoTao = db.TbHeDaoTaos.Where(e => e.MaHeDaoTao.ToString() == HeDaoTao).First().TenHeDaoTao;

                var result = (from p in db.TbThiSinhs
                              join q in db.TbChuyenNganhs on p.MaChuyenNganh equals q.MaChuyenNganh
                              join g in db.TbHeDaoTaos on p.MaHeDaoTao equals g.MaHeDaoTao
                              join k in db.TbNganhs on q.IDNganh equals k.IDNganh
                              join h in db.TbChuyenNganhs on p.MaChuyenNganh equals h.MaChuyenNganh
                              join l in db.TbKhoiThis on k.MaKhoiThi equals l.MaKhoiThi
                              where   k.MaKhoiThi == KhoiThi && p.MaHeDaoTao.ToString() == HeDaoTao && k.IDNganh.ToString() == Nganh
                              select new ResultTK
                              {
                                  MaTS = p.MaTS,
                                  FullName = p.HoDem + p.Ten,
                                  SBD = p.SBD,
                                  NgaySinh = p.NgaySinh,
                                  MaChuyenNganh = p.MaChuyenNganh,
                                  MaHeDaoTao = p.MaHeDaoTao,
                                  MaKhoiThi = l.MaKhoiThi,
                                  TenKhoiThi = l.TenKhoiThi,
                                  MaNganh = k.MaNganh,
                                  TenChuyenNganh = q.TenChuyenNganh,
                                  TenNganh = k.TenNganh,
                                  TenHeDaoTao = g.TenHeDaoTao,
                                  NgayDangKy = p.NgayDangKi
                              }
                              ).AsEnumerable().Where(p => p.NgayDangKy.Date >= _TuNgay.Date && p.NgayDangKy.Date <= _DenNgay.Date); ;

                return PartialView("_DangKyTheoChuyenNganh", result.ToList());
            }
            else
                ModelState.AddModelError("Error", "Có lỗi.");

            return PartialView();
        }

        [HttpPost]
        [ActionName("DangKyTheoHDT")]
        public ActionResult POST_DangKyTheoHDT(string TuNgay, string DenNgay)
        {
            //if (!CheckRole.IsRole("tk_dkthdt", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            if (ModelState.IsValid)
            {
                ViewBag.TuNgay = TuNgay;
                ViewBag.DenNgay = DenNgay;
                DateTime _TuNgay = DateTime.ParseExact(TuNgay, "dd/MM/yyyy", null);
                DateTime _DenNgay = DateTime.ParseExact(DenNgay, "dd/MM/yyyy", null);
                var result = (from p in db.TbThiSinhs
                              join q in db.TbChuyenNganhs on p.MaChuyenNganh equals q.MaChuyenNganh
                              join h in db.TbHeDaoTaos on p.MaHeDaoTao equals h.MaHeDaoTao
                              join k in db.TbNganhs on q.IDNganh equals k.IDNganh
                              join l in db.TbKhoiThis on k.MaKhoiThi equals l.MaKhoiThi
                              //where  
                              select new ResultTK
                              {
                                  MaTS = p.MaTS,
                                  FullName = p.HoDem + p.Ten,
                                  SBD = p.SBD,
                                  NgaySinh = p.NgaySinh,
                                  MaChuyenNganh = p.MaChuyenNganh,
                                  MaHeDaoTao = p.MaHeDaoTao,
                                  MaKhoiThi = l.MaKhoiThi,
                                  TenKhoiThi = l.TenKhoiThi,
                                  MaNganh = k.MaNganh,
                                  TenChuyenNganh = q.TenChuyenNganh,
                                  TenNganh = k.TenNganh,
                                  TenHeDaoTao = h.TenHeDaoTao,
                                  NgayDangKy = p.NgayDangKi
                              }
                             ).AsEnumerable().Where(p => p.NgayDangKy.Date >= _TuNgay.Date && p.NgayDangKy.Date <= _DenNgay.Date); ;

                return PartialView("_DangKyTheoHDT", result.ToList());
            }
            else
                ModelState.AddModelError("Error", "Có lỗi.");

            return PartialView();
        }
        [HttpPost]
        //[ActionName("DangKyTheoHDT")]
        public ActionResult DangKyTheoKhoiThi(string TuNgay, string DenNgay)
        {
            //if (!CheckRole.IsRole("tk_dktkt", Session["Function"] as string[]))
            //    return Redirect("~/accessdenied.html");

            if (ModelState.IsValid)
            {
                //var dbc=DateTime.Now.Date;
                ViewBag.TuNgay = TuNgay;
                ViewBag.DenNgay = DenNgay;
                DateTime _TuNgay = DateTime.ParseExact(TuNgay, "dd/MM/yyyy", null);
                DateTime _DenNgay = DateTime.ParseExact(DenNgay, "dd/MM/yyyy", null);
                var result = (from p in db.TbThiSinhs
                              join q in db.TbChuyenNganhs on p.MaChuyenNganh equals q.MaChuyenNganh
                              join h in db.TbHeDaoTaos on p.MaHeDaoTao equals h.MaHeDaoTao
                              join k in db.TbNganhs on q.IDNganh equals k.IDNganh
                              join l in db.TbKhoiThis on k.MaKhoiThi equals l.MaKhoiThi                              
                              select new ResultTK
                              {
                                  MaTS = p.MaTS,
                                  FullName = p.HoDem + p.Ten,
                                  SBD = p.SBD,
                                  NgaySinh = p.NgaySinh,
                                  MaChuyenNganh = p.MaChuyenNganh,
                                  MaHeDaoTao = p.MaHeDaoTao,
                                  MaKhoiThi = l.MaKhoiThi,
                                  TenKhoiThi = l.TenKhoiThi,
                                  MaNganh = k.MaNganh,
                                  TenChuyenNganh = q.TenChuyenNganh,
                                  TenNganh = k.TenNganh,
                                  TenHeDaoTao = h.TenHeDaoTao,
                                  NgayDangKy = p.NgayDangKi
                              }
                             ).AsEnumerable().Where(p => p.NgayDangKy.Date >= _TuNgay.Date && p.NgayDangKy.Date <= _DenNgay.Date); ;

                return PartialView("_DangKyTheoKhoi", result.ToList());
            }
            else
                ModelState.AddModelError("Error", "Có lỗi.");

            return PartialView();
        }
    }
}